/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mwt.xml.xdbforms.dblayer.query.impl.datasource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;
import mwt.xml.xdbforms.dblayer.ConnectionFactory;
import mwt.xml.xdbforms.dblayer.query.Query;
import mwt.xml.xdbforms.dblayer.query.SQLOperation;
import mwt.xml.xdbforms.dblayer.query.exception.QueryException;
import mwt.xml.xdbforms.dblayer.query.impl.JDBCQueryParameterBuilder;
import mwt.xml.xdbforms.dblayer.query.impl.QueryParameterBuilderDirector;
import mwt.xml.xdbforms.dblayer.query.impl.SQLInsert;
import mwt.xml.xdbforms.dblayer.query.impl.SQLQuery;
import mwt.xml.xdbforms.dblayer.query.impl.SQLUpdate;

/**
 *
 * @author Gianfranco Murador
 */
public class JDBCQuery implements Query {

    private DataSource dataSource;

    public JDBCQuery(DataSource dataSource) {
        this.dataSource = dataSource;


    }

    public void insert(SQLOperation operation) throws QueryException {
        Connection c = null;
        PreparedStatement ps = null;
        QueryParameterBuilderDirector builderDirector =
                new QueryParameterBuilderDirector(new JDBCQueryParameterBuilder(operation));


        if (!(operation instanceof SQLInsert)) {
            throw new QueryException("opertaion not supported,SQLInsert required");
        }

        try {
            c = dataSource.getConnection();
            String db = ConnectionFactory.getDatabase();
            Statement s = c.createStatement();
            s.execute("USE " + db);
            s.close();

            ps = builderDirector.createInsertStatement(c);
            ps.executeUpdate();

        } catch (SQLException ex) {
            throw new QueryException("Insert Error", ex);
        } finally {

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }


        }

        builderDirector = null;



    }

    public void update(SQLOperation operation) throws QueryException {
        Connection c = null;
        PreparedStatement ps = null;
        QueryParameterBuilderDirector builderDirector =
                new QueryParameterBuilderDirector(new JDBCQueryParameterBuilder(operation));

        if (!(operation instanceof SQLUpdate)) {
            throw new QueryException("opertaion not supported, required SQLUpdate");
        }

        try {
            c = dataSource.getConnection();
            String db = ConnectionFactory.getDatabase();
            Statement s = c.createStatement();
            s.execute("USE " + db);
            s.close();

            ps = builderDirector.createUpdateStatement(c);

            System.out.println("update statement " + ps.toString());

            ps.executeUpdate();

        } catch (SQLException ex) {
            throw new QueryException("Insert Error", ex);
        } finally {

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }


        }

        builderDirector = null;

    }

    public Collection read(String table, int start, int length) throws QueryException {


        String sql = "SELECT * FROM ? LIMIT ?,?";
        ResultSet rs = null;
        Connection c = null;
        Set<List<String>> set = new HashSet<List<String>>();
        List<String> rowlist = null;


        PreparedStatement ps = null;
        try {
            c = dataSource.getConnection();
            String db = ConnectionFactory.getDatabase();
            Statement s = c.createStatement();
            s.execute("USE " + db);
            s.close();


            ps = c.prepareStatement(sql);
            ps.setString(1, table);
            ps.setInt(2, start);
            ps.setInt(3, length);
            rs = ps.executeQuery();

            int numColumns = rs.getMetaData().getColumnCount();

            while (rs.next()) { // Per ogni riga

                //String item = rs.getString(i);
                // per ogni colonna
                rowlist = new ArrayList<String>();
                for (int i = 1; i <= numColumns; i++) {

                    rowlist.add(rs.getString(i));
                }

                set.add(rowlist);

            }




        } catch (SQLException ex) {
            throw new QueryException("Read error", ex);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();

                }
            }

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }

            }

        }
        return set;

    }

    public Collection read(String table) throws QueryException {

        String sql = "SELECT * FROM " + table;
        ResultSet rs = null;
        Connection c = null;
        PreparedStatement ps = null;
        Set<List<String>> set = new HashSet<List<String>>();
        List<String> rowlist = null;

        String db = ConnectionFactory.getDatabase();


        try {
            c = dataSource.getConnection();
            Statement s = c.createStatement();
            s.execute("USE " + db);
            s.close();

            ps = c.prepareStatement(sql);
            //ps.setString(1, table);
            rs = ps.executeQuery();

            int numColumns = rs.getMetaData().getColumnCount();

            while (rs.next()) { // Per ogni riga

                //String item = rs.getString(i);
                // per ogni colonna
                rowlist = new ArrayList<String>();
                for (int i = 1; i <= numColumns; i++) {

                    rowlist.add(rs.getString(i));
                }

                set.add(rowlist);

            }




        } catch (SQLException ex) {
            throw new QueryException("Read error", ex);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JDBCQuery.class.getName()).log(Level.SEVERE, null, ex);
                }
            }

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JDBCQuery.class.getName()).log(Level.SEVERE, null, ex);
                }

            }

        }
        return set;
    }

    public Collection read(SQLOperation operation) throws QueryException {


        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Set set = new HashSet();
        List<String> rowlist = null;

        QueryParameterBuilderDirector builderDirector =
                new QueryParameterBuilderDirector(new JDBCQueryParameterBuilder(operation));

        if (!(operation instanceof SQLQuery)) {
            throw new QueryException("opertaion not supported, SQLQuery required");
        }


        try {
            c = dataSource.getConnection();
            String db = ConnectionFactory.getDatabase();
            Statement s = c.createStatement();
            s.execute("USE " + db);
            s.close();

            ps = builderDirector.createSelectStatement(c);


            System.out.println(ps.toString());

            rs = ps.executeQuery();


            int numColumns = rs.getMetaData().getColumnCount();

            while (rs.next()) { // Per ogni riga

                //String item = rs.getString(i);
                // per ogni colonna
                rowlist = new ArrayList<String>();
                for (int i = 1; i <= numColumns; i++) {

                    rowlist.add(rs.getString(i));
                }

                set.add(rowlist);

            }


        } catch (SQLException ex) {
            throw new QueryException("Insert Error", ex);
        } finally {

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }


        }

        builderDirector = null;
        return set;

    }

    public String getInsertStmt(SQLOperation operation) throws QueryException {
        Connection c = null;
        PreparedStatement ps = null;
        String stmt = null;
        QueryParameterBuilderDirector builderDirector =
                new QueryParameterBuilderDirector(new JDBCQueryParameterBuilder(operation));


        if (!(operation instanceof SQLInsert)) {
            throw new QueryException("opertaion not supported,SQLInsert required");
        }

        try {
            c = dataSource.getConnection();
            String db = ConnectionFactory.getDatabase();
            Statement s = c.createStatement();
            s.execute("USE " + db);
            s.close();

            ps = builderDirector.createInsertStatement(c);
            ps.execute();
            stmt = ps.toString();

        } catch (SQLException ex) {
            throw new QueryException("Insert Error", ex);
        } finally {

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }


        }

        builderDirector = null;
        return stmt;


    }

    public String getUpdateStmt(SQLOperation operation) throws QueryException {
        Connection c = null;
        PreparedStatement ps = null;
        String stmt = null;
        QueryParameterBuilderDirector builderDirector =
                new QueryParameterBuilderDirector(new JDBCQueryParameterBuilder(operation));

        if (!(operation instanceof SQLUpdate)) {
            throw new QueryException("opertaion not supported, required SQLUpdate");
        }
        try {
            c = dataSource.getConnection();
            String db = ConnectionFactory.getDatabase();
            Statement s = c.createStatement();
            s.execute("USE " + db);
            s.close();

            ps = builderDirector.createUpdateStatement(c);
            ps.execute();
            stmt = ps.toString();


        } catch (SQLException ex) {
            throw new QueryException(ex.getMessage(), ex);
        } finally {

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }


        }

        builderDirector = null;

        return stmt;


    }
}
